set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


with tmp as (
select *
from (
    select
         waybill_no
        ,order_source_code
        ,order_source_name
        ,case when duty_type = '发运超时36H'         then scan_site_code
              when duty_type = '首中心停滞36H'       then scan_site_code
              when duty_type = '【网点】干线停滞+1D' then scan_site_code
              when duty_type = '中心停滞36H'         then scan_site_code
              when duty_type = '【中心】干线停滞+1D' then scan_site_code
              when duty_type = '中心停滞36H'         then scan_site_code
              when duty_type = '支线停滞36H'         then scan_site_code
              when duty_type = '超时未派24H'         then scan_site_code
              when duty_type = '派签超时24H'         then scan_site_code
              else scan_site_code end as scan_site_code
        ,late_type
        ,duty_type
        ,scan_time
        ,next_time
        ,oper_time
        ,if_late
        ,next_site_code
        ,next_site_name
        ,next_site_type
        ,if_ydlf_36h
        ,if_yfwx_36h
        ,oper_date
        ,start_network_code       --始发网点编码
        ,start_network_name       --始发网点名称
        ,end_network_code         --目的网点编码
        ,end_network_name         --目的网点名称
        ,type
        ,dt
        ,count(1) over(partition by waybill_no) as oper_cnt
    from (
        select
             waybill_no              as waybill_no               --运单号
            ,order_source_code       as order_source_code        --订单来源编码
            ,order_source_name       as order_source_name        --订单来源名称
            ,scan_site_code          as scan_site_code           --当前站点编码
            ,case when type = 6 then 4 else type end as type
            ,case when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type in (1,2,3) then '发运超时'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type in (4,5,6,7) then '中转超时'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type in (8,9) then '派件超时'
                  end as late_type --超时环节                         
            ,case when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 1 then '发运超时36H'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 2 then '首中心停滞36H'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 3 then '【网点】干线停滞+1D'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 4 then '中心停滞36H'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 5 then '【中心】干线停滞+1D'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 6 then '中心停滞36H'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 7 then '支线停滞36H'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 8 then '超时未派24H'
                  when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time and type = 9 then '派签超时24H'
                  end as duty_type --异常环节
            ,scan_time as scan_time  --当前扫描时间
            ,nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) as next_time  --下个扫描时间
            ,oper_time as oper_time  --应操作时间
            ,case when nvl(next_site_time,if(reback_apply_time is not null,'0000-00-00 00:00:00','{{ execution_date | cst_ds }} 23:59:59')) > oper_time then '是' else '否' end as if_late --是否超时
            ,send_next_site_code as next_site_code   --发件下一站编码 --下一站编码
            ,send_next_site_name as next_site_name   --发件下一站名称 --下一站名称
            ,send_next_site_type as next_site_type --下一站类型
            ,if_ydlf_36h as if_ydlf_36h --是否有到漏发超36小时
            ,if_yfwx_36h as if_yfwx_36h --是否有发未卸超36小时
            ,to_date(oper_time) as oper_date --应操作日期
            ,start_network_code       --始发网点编码
            ,start_network_name       --始发网点名称
            ,end_network_code         --目的网点编码
            ,end_network_name         --目的网点名称
            ,to_date(oper_time) as dt --分区日期
        from (
            select
                 waybill_no
                ,order_source_code       as order_source_code  --订单来源编码
                ,order_source_name       as order_source_name  --订单来源名称
                ,start_network_code      as start_network_code --始发网点编码
                ,start_network_name      as start_network_name --始发网点名称
                ,end_network_code        as end_network_code   --目的网点编码
                ,end_network_name        as end_network_name   --目的网点名称
                ,nvl(send_next_site_code,next_site_code) as send_next_site_code  --发件下一站编码 --下一站编码
                ,nvl(send_next_site_name,next_site_name) as send_next_site_name  --发件下一站名称 --下一站名称
                ,nvl(send_next_site_type,next_site_type) as send_next_site_type  --下一站类型
                ,scan_site_no
                ,scan_site_code
                ,scan_site_name
                ,scan_site_type
                ,reback_apply_time
                ,aging_sign_time
                ,tmp_time[0] as scan_time --每个站点的扫描时间
                ,coalesce(lead(tmp_time[0]) over(partition by waybill_no order by tmp_time[0]),aging_sign_time,'{{ execution_date | cst_ds }} 23:59:59') as next_site_time --下一站发件时间
                ,case when scan_site_type = 4 and scan_site_send_time is null and scan_site_arrival_time is not null
                      and unix_timestamp(next_site_arrival_time) - unix_timestamp(scan_site_arrival_time) > 36 * 60 * 60  then 1 else 0 end as if_ydlf_36h --是否有到漏发超36小时
                ,case when scan_site_type = 4 and scan_site_send_time < scan_site_arrival_time --实际上一站做了发件
                      and unix_timestamp(nvl(next_site_arrival_time,'{{ execution_date | cst_ds }} 23:59:59')) - unix_timestamp(scan_site_arrival_time) > 36 * 60 * 60  then 1 else 0 end as if_yfwx_36h --是否有发未卸超36小时
                ,case when scan_site_no = 1 and scan_site_type = 6  and tmp_time[0] = taking_time then from_unixtime(unix_timestamp(taking_time) + 36 * 60 * 60,'yyyy-MM-dd HH:mm:ss')   --发运36H
                      when scan_site_center_no = 1 and scan_site_type = 4 and tmp_time[0] = scan_site_arrival_time and next_site_type = 4 then from_unixtime(unix_timestamp(scan_site_arrival_time) + 36 * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --首中心到件36H
                      when scan_site_center_no = 1 and scan_site_type = 4 and tmp_time[0] = scan_site_send_time and next_site_type = 4 then from_unixtime(unix_timestamp(nvl(scan_site_send_time,scan_site_arrival_time)) + (config.platform_effect * 24 + 24) * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --始发中心干线停滞
                      when scan_site_type = 4 and tmp_time[0] = scan_site_arrival_time and next_site_type = 4 then from_unixtime(unix_timestamp(scan_site_arrival_time) + 36 * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --中转中心到件停滞36H
                      when scan_site_type = 4 and tmp_time[0] = scan_site_send_time and next_site_type = 4 then from_unixtime(unix_timestamp(nvl(scan_site_send_time,scan_site_arrival_time)) + (config.platform_effect * 24 + 24) * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --干线停滞
                      when scan_site_type = 4 and tmp_time[0] = scan_site_arrival_time and next_site_type in (5,6) then from_unixtime(unix_timestamp(scan_site_arrival_time) + 36 * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --末中心到件36H
                      when scan_site_type = 4 and tmp_time[0] = scan_site_send_time and next_site_type in (5,6) then from_unixtime(unix_timestamp(nvl(scan_site_send_time,scan_site_arrival_time)) + 36 * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --支线停滞
                      when scan_site_no <> 1 and scan_site_type = 6 and tmp_time[0] = scan_site_arrival_time then from_unixtime(unix_timestamp(scan_site_arrival_time) + 24 * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --网点到件时间+24
                      when scan_site_no <> 1 and scan_site_type = 6 and tmp_time[0] = deliver_time then from_unixtime(unix_timestamp(deliver_time) + 24 * 60 * 60,'yyyy-MM-dd HH:mm:ss')  --网点出仓
                      end as oper_time  --到件操作时间
                ,case when scan_site_no = 1 and scan_site_type = 6 and tmp_time[0] = taking_time then 1 --始发网点揽收
                      when scan_site_center_no = 1 and scan_site_type = 4 and tmp_time[0] = scan_site_arrival_time and next_site_type = 4 then 2 --始发中心到件
                      when scan_site_center_no = 1 and scan_site_type = 4 and tmp_time[0] = scan_site_send_time and next_site_type = 4 then 3 --始发中心发件
                      when scan_site_type = 4 and tmp_time[0] = scan_site_arrival_time and next_site_type = 4 then 4 --中转中心到件
                      when scan_site_type = 4 and tmp_time[0] = scan_site_send_time and next_site_type = 4 then 5 --中转中心发件
                      when scan_site_type = 4 and tmp_time[0] = scan_site_arrival_time and next_site_type in (5,6) then 6 --目的中心到件
                      when scan_site_type = 4 and tmp_time[0] = scan_site_send_time and next_site_type in (5,6) then 7 --目的中心发件
                      when scan_site_no <> 1 and scan_site_type = 6 and tmp_time[0] = scan_site_arrival_time and tmp_time[0] <> deliver_time then 8 --目的网点到件
                      when scan_site_no <> 1 and scan_site_type = 6 and tmp_time[0] = deliver_time then 9 --目的网点出仓
                      end as type --类型
            from (
                select
                     *
                    ,nvl(send_next_site_code,lead(scan_site_code        ) over(partition by waybill_no order by scan_site_no)) as next_site_code          --下一站编码
                    ,nvl(send_next_site_name,lead(scan_site_name        ) over(partition by waybill_no order by scan_site_no)) as next_site_name          --下一站名称
                    ,nvl(send_next_site_type,lead(scan_site_type        ) over(partition by waybill_no order by scan_site_no)) as next_site_type          --下一站类型
                    ,lead(scan_site_send_time   ) over(partition by waybill_no order by scan_site_no) as next_site_send_time     --下一站发件时间
                    ,lead(scan_site_arrival_time) over(partition by waybill_no order by scan_site_no) as next_site_arrival_time  --下一站到件时间
                    ,lead(scan_site_city_name   ) over(partition by waybill_no order by scan_site_no) as next_site_city_name     --下一站城市编码
                    ,first_value(case when scan_site_type = 4 then scan_site_arrival_time end) over(partition by waybill_no order by scan_site_type) as first_center_arrival_time  --始发中心规划到车时间
                from (
                    select
                         step.waybill_no              as waybill_no              --运单号
                        ,row_number() over(partition by step.waybill_no order by step.scan_site_no asc) as scan_site_no            --当前站点编码
                        ,step.scan_site_code          as scan_site_code          --当前站点编码
                        ,step.scan_site_name          as scan_site_name          --当前站点名称
                        ,dim.network_type             as scan_site_type          --当前站点类型
                        ,step.taking_time             as taking_time             --揽收时间
                        ,case when step.scan_site_no =  1 and dim.network_type = 6 then nvl(step.taking_time,step.scan_site_send_time)
                              when step.scan_site_no <> 1 and dim.network_type = 6 then nvl(deliver.last_deliver_time,if(aging_sign_time_tmp = '9999-99-99 99:99:99',null,aging_sign_time_tmp))
                              else step.scan_site_send_time end as scan_site_send_time     --发件时间
                        ,case when step.scan_site_no <> 1 and dim.network_type = 6 then nvl(step.scan_site_arrival_time,step.scan_site_send_time)
                              else step.scan_site_arrival_time end as scan_site_arrival_time --到件时间
                        ,deliver.last_deliver_time    as deliver_time            --出仓时间
                        ,step.order_source_code       as order_source_code       --订单来源编码
                        ,step.order_source_name       as order_source_name       --订单来源名称
                        ,dim.city_id                  as scan_site_city_code     --当前站点城市编码
                        ,dim.city_desc                as scan_site_city_name     --当前站点城市名称
                        ,abnormal.reback_apply_time   as reback_apply_time       --退件申请时间
                        ,step.scan_site_center_no     as scan_site_center_no     --中心序号
                        ,step.end_network_code        as end_network_code        --目的网点编码
                        ,step.end_network_name        as end_network_name        --目的网点名称
                        ,dim_start.code               as start_network_code      --始发网点编码
                        ,dim_start.name               as start_network_name      --始发网点名称
                        ,step.send_next_site_code     as send_next_site_code     --发件下一站编码
                        ,dim_send_next.name           as send_next_site_name     --发件下一站名称
                        ,dim_send_next.network_type   as send_next_site_type     --发件下一站名称
                        ,case when aging_sign_time_tmp = '9999-99-99 99:99:99' then null else aging_sign_time_tmp end as aging_sign_time --时效签收时间
                    from (
                        select
                             waybill_no
                            ,ordersource_code                    as order_source_code      --订单来源编码
                            ,ordersource_name                    as order_source_name      --订单来源名称
                            ,dispatch_network_code               as end_network_code       --目的网点编码
                            ,dispatch_network_name               as end_network_name       --目的网点名称
                            ,first_code                          as first_code             --一段码
                            ,send_station_number                 as scan_site_no           --当前站点序号
                            ,send_station_network_code           as scan_site_code         --当前站点编码
                            ,send_station_network_name           as scan_site_name         --当前站点名称
                            ,send_center_station_number          as scan_site_center_no    --当前站点中心编号
                            --,deliver_later_scantime              as deliver_time           --出仓时间
                            ,send_station_arrival_early_time     as scan_site_arrival_time --到件时间
                            ,send_station_send_later_time        as scan_site_send_time    --发件时间
                            ,send_station_send_next_network_code as send_next_site_code     --发件下一站编码
                            ,send_station_next_station
                            ,nvl(taking_early_scantime,warehousing_early_scantime) as taking_time  --揽收
                            ,least(nvl(instore_early_scantime,'9999-99-99 99:99:99'),nvl(outstore_early_scantime,'9999-99-99 99:99:99'),nvl(sign_early_scantime,'9999-99-99 99:99:99')) as aging_sign_time_tmp --实效签收时间
                            ,nvl(taking_early_scansitecode,warehousing_early_scansitecode) as taking_site_code --揽收网点
                        from jms_dwd.dwd_wide_unsign_trace_waybill_step_dt
                        where dt between date_sub('{{ execution_date | cst_ds }}',15) and '{{ execution_date | cst_ds }}'
                        and send_station_network_code is not null --剔除出库网点为空场景
                        and ordersource_name = '紫金山' --目前只看紫金山
                        --and waybill_no = 'JT2062919070459'
                    ) step
                    left join jms_dim.dim_network_whole_massage dim
                       on dim.code = step.scan_site_code --当前站点
                    left join jms_dim.dim_network_whole_massage dim_start
                         on step.taking_site_code = dim_start.code --取到目的网点相关维度
                    left join jms_dim.dim_network_whole_massage dim_send_next
                         on step.send_next_site_code = dim_send_next.code --取到目的网点相关维度
                    left join (
                        select 
                             refund.waybill_no
                            ,nvl(intercept_time,apply_time) as reback_apply_time 
                        from (
                            select
                                 waybill_no
                                ,examine_network_code
                                ,examine_time apply_time
                            from jms_dwd.dwd_tab_reback_transfer_express_base_dt
                            where dt >= date_add(date('{{ execution_date | cst_ds }}'),-30+1) and dt<='{{ execution_date | cst_ds }}'
                              and STATUS = '2' --面单是否已打印 1是 0否
                              group by waybill_no, examine_time,examine_network_code
                        ) refund
                        left join (
                            select
                                  min(intercept_time) intercept_time
                                  ,waybill_no
                                  ,intercept_network_code as intercept_site_code
                            from jms_dwd.dwd_yl_oms_interceptorpiece_base
                            where to_date(intercept_time) >= date_add(date('{{ execution_date | cst_ds }}'),-30+1) and to_date(intercept_time) <='{{ execution_date | cst_ds }}'
                            group by waybill_no,intercept_network_code
                        ) intercept on refund.waybill_no = intercept.waybill_no
                                   and intercept.intercept_site_code = refund.examine_network_code
                    ) abnormal on step.waybill_no = abnormal.waybill_no
                    left join (
                        select
                             waybill_no
                            ,scan_site_code
                            ,min(scan_time) as first_deliver_time
                            ,max(scan_time) as last_deliver_time
                        from jms_dwd.dwd_tab_barscan_deliver_base_dt
                        where dt between date_sub('{{ execution_date | cst_ds }}',21) and '{{ execution_date | cst_ds }}'
                        group by waybill_no
                                ,scan_site_code
                    ) deliver on deliver.waybill_no = step.waybill_no
                             and deliver.scan_site_code = step.scan_site_code
                ) step
            ) step
            left join jms_dim.dim_tab_platform_effect_config_base config --平台时效配置表
                on step.scan_site_city_name = config.start_city
               and step.next_site_city_name = config.end_city
               and step.order_source_name = config.platform_name
               and step.taking_time between config.effect_start_time and config.effect_end_time
               and config.tctatus = 1
            lateral view explode(array(array(scan_site_send_time),array(scan_site_arrival_time))) tmp as tmp_time
            where tmp_time[0] >= taking_time --剔除揽收前的轨迹
              and tmp_time[0] <= nvl(reback_apply_time,'9999-99-99 99:99:99') --剔除退件后的轨迹
              and tmp_time[0] <= nvl(aging_sign_time  ,'9999-99-99 99:99:99') --剔除时效签收后的轨迹
              and !(scan_site_type = 5 and tmp_time[0] <= first_center_arrival_time)
              and tmp_time[0] is not null
        ) a where to_date(oper_time) = '{{ execution_date | cst_ds }}' --限制应操作时间
             and type is not null
    ) base
) base
--where base.next_time > base.oper_time --只保留异常数据
--and duty_type is not null
)






insert overwrite table jms_dm.dm_waybill_fayun_late_detail_dt
select *
from (
    select
         base.waybill_no
        ,base.order_source_code
        ,base.order_source_name
        ,base.scan_site_code
        ,dim.name           as scan_site_name
        ,dim.network_type   as scan_site_type
        ,dim.city_id        as scan_site_city_code      --当前站点所属城市编码
        ,dim.city_desc      as scan_site_city_name      --当前站点所属城市名称
        ,dim.provider_id    as scan_site_provider_code  --当前站点所属省份编码
        ,dim.provider_desc  as scan_site_provider_name  --当前站点所属省份名称
        ,dim.agent_code     as scan_site_agent_code     --当前站点所属代理区编码
        ,dim.agent_name     as scan_site_agent_name     --当前站点所属代理区名称
        ,case when duty_type = '首中心停滞36H'       then '中转超时'  
              when duty_type = '【网点】干线停滞+1D' then '中转超时' 
              else base.late_type end as late_type
        ,case when duty_type = '首中心停滞36H'       then '中心停滞36H'  
              when duty_type = '【网点】干线停滞+1D' then '【中心】干线停滞+1D' 
              else duty_type end as duty_type
        ,base.scan_time
        ,base.next_time
        ,base.oper_time
        ,base.if_late
        ,base.next_site_code
        ,base.next_site_name
        ,base.next_site_type
        ,null as scan_type
        ,base.if_ydlf_36h
        ,base.if_yfwx_36h
        ,base.oper_date
        ,base.oper_cnt
        ,case when duty_type = '首中心停滞36H'       then 4  
              when duty_type = '【网点】干线停滞+1D' then 5
              else base.type end as ype
        ,base.dt
    from tmp base
    left join jms_dim.dim_network_whole_massage dim
       on dim.code = base.scan_site_code --当前
    union all
    select
         base.waybill_no
        ,base.order_source_code
        ,base.order_source_name
        ,base.scan_site_code
        ,dim.name           as scan_site_name
        ,dim.network_type   as scan_site_type
        ,dim.city_id        as scan_site_city_code      --当前站点所属城市编码
        ,dim.city_desc      as scan_site_city_name      --当前站点所属城市名称
        ,dim.provider_id    as scan_site_provider_code  --当前站点所属省份编码
        ,dim.provider_desc  as scan_site_provider_name  --当前站点所属省份名称
        ,dim.agent_code     as scan_site_agent_code     --当前站点所属代理区编码
        ,dim.agent_name     as scan_site_agent_name     --当前站点所属代理区名称
        ,base.late_type
        ,base.duty_type
        ,base.scan_time
        ,base.next_time
        ,base.oper_time
        ,base.if_late
        ,base.next_site_code
        ,base.next_site_name
        ,base.next_site_type
        ,null as scan_type
        ,base.if_ydlf_36h
        ,base.if_yfwx_36h
        ,base.oper_date
        ,base.oper_cnt
        ,base.type
        ,base.dt
    from (
        select
             waybill_no
            ,order_source_code
            ,order_source_name
            ,case when duty_type = '首中心停滞36H'       then start_network_code
                  when duty_type = '【网点】干线停滞+1D' then start_network_code
                  else scan_site_code end as scan_site_code
            ,late_type
            ,duty_type
            ,scan_time
            ,next_time
            ,oper_time
            ,if_late
            ,next_site_code
            ,next_site_name
            ,next_site_type
            ,if_ydlf_36h
            ,if_yfwx_36h
            ,oper_date
            ,oper_cnt
            ,type
            ,dt
        from tmp
        where duty_type in ('首中心停滞36H','【网点】干线停滞+1D') --异常操作
        union all
        select
             waybill_no
            ,order_source_code
            ,order_source_name
            ,start_network_code as scan_site_code
            ,late_type
            ,duty_type
            ,scan_time
            ,next_time
            ,oper_time
            ,if_late
            ,next_site_code
            ,next_site_name
            ,next_site_type
            ,if_ydlf_36h
            ,if_yfwx_36h
            ,oper_date
            ,oper_cnt
            ,type
            ,dt
        from tmp
        where type in (2,3) --正常操作
        and duty_type is null
    ) base
    left join jms_dim.dim_network_whole_massage dim
       on dim.code = base.scan_site_code --当前
) a where scan_site_code is not null
distribute by dt,pmod(hash(rand()),80);
    
    




